import os
import mysql.connector
import alibabacloud_oss_v2 as oss

# 硬编码参数值（与uploads.py保持一致）
region = 'cn-beijing'
bucket = 'whatteam1'
# key = 'a.png'

# 硬编码凭证信息（与uploads.py相同）
access_key_id = "LTAI5tC4VPs9n2ZoLy8sptqR"
access_key_secret = "Gr6QeyWXCuwhTVVUk25x8FTTMLunwv"

def deletoss(key:str):
    # 使用静态凭证信息
    credentials_provider = oss.credentials.StaticCredentialsProvider(
        access_key_id=access_key_id,
        access_key_secret=access_key_secret
    )

    # 加载SDK配置
    cfg = oss.config.load_default()
    cfg.credentials_provider = credentials_provider
    cfg.region = region

    # 创建OSS客户端
    client = oss.Client(cfg)

    # 执行删除操作
    result = client.delete_object(oss.DeleteObjectRequest(
        bucket=bucket,
        key=key,
    ))

    # 输出完整结果信息
    print(f'Delete successful! Target: {bucket}/{key}')
    print(f'status code: {result.status_code},'
          f' request id: {result.request_id},'
          f' version id: {result.version_id},'
          f' delete marker: {result.delete_marker}'
          )

def delete_textbooks_with_special_path():
    # 数据库连接配置
    config = {
        'user': 'root',
        'password': '123456',
        'host': 'localhost',
        'database': 'ruanjianbei',
        'port': 3308,
        'raise_on_warnings': True
    }

    cnx = cursor = None
    try:
        # 连接数据库
        cnx = mysql.connector.connect(**config)
        cursor = cnx.cursor()

        # 1. 先查询要删除的记录及其文件路径
        select_query = """
        SELECT path FROM textbook
        WHERE path LIKE '%初中地理%'
        """
        cursor.execute(select_query)
        records = cursor.fetchall()

        # 2. 删除对应的文件
        base_path = "D:/aaaaazzz/union_project/union_project1"
        deleted_files = 0
        for (path,) in records:
            file_path = os.path.join(base_path, path)
            deletoss(path)
            try:
                if os.path.exists(file_path):
                    os.remove(file_path)
                    deleted_files += 1
                    print(f"Deleted file: {file_path}")
                else:
                    print(f"File not found: {file_path}")
            except Exception as e:
                print(f"Error deleting file {file_path}: {e}")

        # 3. 删除数据库记录
        delete_query = """
        DELETE FROM textbook
        WHERE path LIKE '%初中地理%'
        """
        cursor.execute(delete_query)
        cnx.commit()

        print(f"Successfully deleted {cursor.rowcount} database records and {deleted_files} files")

    except mysql.connector.Error as err:
        print(f"Database error: {err}")
        if cnx: cnx.rollback()
    except Exception as e:
        print(f"General error: {e}")
        if cnx: cnx.rollback()
    finally:
        if cursor: cursor.close()
        if cnx: cnx.close()

if __name__ == "__main__":
    delete_textbooks_with_special_path()
